﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using PPI.Entities;
using PPI.Entities.Distribution;
using PPI.Entities.Enumerator;
using PPI.Entities.Inspections;
using PPI.Entities.Lookup;
using PPI.General;
using PPI.Mobile.Logic;
using VMind.Global;
using ENote=PPI.Entities.Distribution.ENote;

namespace PPI.Client.Logic.Distribution
{
    public class LPole
    {
        public static Int32 Save(EDistributionPole pole)
        {
            return WebConsumer.Distribution.WPole.Save(pole);
        }

        public static void Update(EDistributionPole pole, List<EDistributionPoleImage> imagesToDelete)
        {
            //Update Pole fields
            if (pole.Map == null)
                pole.Map = new EMap(int.MinValue);

            if (pole.Specie == null)
                pole.Specie = new EXMissionStructures_PoleSpecies(int.MinValue);

            if (pole.Class == null)
                pole.Class = new EXMissionStructures_PoleClass(int.MinValue);

            if (pole.Treatment == null)
                pole.Treatment = new EXMissionStructures_PoleTreatment(int.MinValue);

            if (pole.Length == null)
                pole.Length = new EXMissionStructures_PoleLength(int.MinValue);

            if (pole.InspectionResult.InspectionType == null)
                pole.InspectionResult.InspectionType = new EInspectionTypes(int.MinValue);

            if (pole.InspectionResult.Resultype == null)
                pole.InspectionResult.Resultype = new EResultType(int.MinValue);

            WebConsumer.Distribution.WPole.Update(pole);

            //Update Pole Phases
            pole.Phases = WebConsumer.Distribution.WDistributionPolePhase.Update(pole.Phases, pole.Id);

            //Update Pole Tops
            pole.Tops = WebConsumer.Distribution.WDistributionPoleTop.Update(pole.Tops, pole.Id);

            //Update Pole Notes
            pole.Notes = WebConsumer.Distribution.WPole.Update(pole.Id, pole.Notes);

            //Update Inspection Result
            WebConsumer.Distribution.WPole.Update(pole.InspectionResult, pole.Id);

            //Images to be deleted
            int[] poleImagesToDelete = new int[imagesToDelete.Count];

            for (int i = 0; i < imagesToDelete.Count; i++)
                poleImagesToDelete[i] = imagesToDelete[i].Id;

            WebConsumer.Distribution.WPole.DeletePoleImages(poleImagesToDelete);

            //Images to be uploaded
            WebUploaderHelper _UploaderHelper = new WebUploaderHelper(Constants.K_WEBSITEUPLOADER);

            foreach (EDistributionPoleImage newImage in pole.Images)
            {
                if (newImage.Id == int.MinValue)
                {
                    //Upload pole Images
                    newImage.ServerID = SavePoleImage(newImage, pole.Id);
                    newImage.Id = newImage.ServerID;

                    string _imageDirectory = DirHelper.GetImagesDirectory(newImage.GetDirectoryName(pole.Id));
                    //FileInfo file = new FileInfo(_imageDirectory + Path.DirectorySeparatorChar + newImage.FileName);
                    FileInfo file = new FileInfo(newImage.FullName);
                    string _FileName = pole.Id + "_" + newImage.ServerID + ".jpg";
                    string _FolderName = DirHelper.GetImageDirectory(pole.Id, newImage.ServerID);
                    newImage.FileName = _FileName;

                    if (file.Exists)
                        _UploaderHelper.Upload(_FolderName, _FileName, file, Enum.GetName(typeof(DatabaseTypes), DatabaseTypes.DistributionInspection));
                }
            }
        }

        public static Int32 SavePoleImage(EDistributionPoleImage image, Int32 idPole)
        {
            return WebConsumer.Distribution.WPole.Save(image.FileName, idPole);
        }

        public static void SaveMeterImage(EDistributionPoleImage image, Int32 idMeter)
        {
            WebConsumer.Distribution.WPole.SaveMeterImage(image.FileName, idMeter);
        }

        public static Int32 SaveTransformerImage(EDistributionPoleImage image, Int32 idMeter)
        {
            return WebConsumer.Distribution.WPole.SaveTransformerImage(image.FileName, idMeter);
        }

        public static Int32 SaveSectionalizerImage(EDistributionPoleImage image, Int32 idSectionalizer)
        {
            return WebConsumer.Distribution.WPole.SaveSectionalizerImage(image.FileName, idSectionalizer);
        }

        public static Int32 SaveRecloserImage(EDistributionPoleImage image, Int32 idRecloser)
        {
            return WebConsumer.Distribution.WPole.SaveRecloserImage(image.FileName, idRecloser);
        }

        public static void UpdateUploadedData(EDistributionInspectionResult inspectionResult, Int32 poleId, string connectionString)
        {
            StringBuilder _query = new StringBuilder();
            _query.AppendLine("UPDATE tb_Pole_InspectionResult set PoleInspectionResultServerID = " + inspectionResult.ServerId + ", PoleServerID = " + poleId + ", UploadedDate = " +
                              DateTime.Now.ToString("yyyy-MM-dd") + " where PoleInspectionResultId =" + inspectionResult.Id + " ");
            
            LMobileDatabase.ExecuteQuery(_query.ToString(), connectionString);
        }

        public static void UpdateUploadedData(EDistributionPole pole, string connectionString)
        {
            StringBuilder _query = new StringBuilder();
            _query.AppendLine("update tb_Poles set PoleServerID =" + pole.ServerID + ", UploadedDate = " +
                              DateTime.Now.ToString("yyyy-MM-dd") + " where PoleId =" + pole.Id + "  ");
            LMobileDatabase.ExecuteQuery(_query.ToString(), connectionString);
        }

        public static List<EDistributionPole> GetDistributionImages(string connectionString)
        {
            StringBuilder _sql = new StringBuilder();

            #region Query

            _sql.AppendLine("SELECT ");
            _sql.AppendLine("   P.PoleId, ");
            _sql.AppendLine("   P.PoleServerID ");
            _sql.AppendLine("FROM tb_Poles P ");

            #endregion

            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            List<EDistributionPole> lstDistributionPoles = new List<EDistributionPole>();

            foreach (DataRow dr in dt.Rows)
            {
                EDistributionPole distributionPole = new EDistributionPole();

                distributionPole.Id = ConvertHelper.ToInt32(dr["PoleId"]);
                distributionPole.ServerID = ConvertHelper.ToInt32(dr["PoleServerID"]);

                distributionPole.Images = LImage.GetPoleImages(distributionPole, connectionString);
                distributionPole.Meters = LMeter.GetPoleMeters(distributionPole, connectionString);
                distributionPole.Transformers = LTransformer.GetPoleTransformers(distributionPole, connectionString);
                distributionPole.Sectionalizers = LSectionalizer.GetPoleSectionalizers(distributionPole, connectionString);
                distributionPole.Reclosers = LRecloser.GetPoleReclosers(distributionPole, connectionString);


                lstDistributionPoles.Add(distributionPole);
            }

            return lstDistributionPoles;
        }

        public static List<EDistributionPole> GetDistributionPoles(string connectionString)
        {
            StringBuilder _sql = new StringBuilder();

            #region Query

            _sql.AppendLine("SELECT ");
            _sql.AppendLine("   P.PoleId, ");
            _sql.AppendLine("   P.PoleServerID, ");
            _sql.AppendLine("   P.PolygonMapFk, ");
            _sql.AppendLine("   P.PolygonSectionFk, ");
            _sql.AppendLine("   P.SpecieFk, ");
            _sql.AppendLine("   P.ClassFk, ");
            _sql.AppendLine("   P.LengthFk, ");
            _sql.AppendLine("   P.D804, ");
            _sql.AppendLine("   P.TreatmentFk, ");
            _sql.AppendLine("   P.Latitude, ");
            _sql.AppendLine("   P.Longitude, ");
            _sql.AppendLine("   P.InspBy, ");
            _sql.AppendLine("   P.Number, ");
            _sql.AppendLine("   P.AcquisitionDate, ");
            _sql.AppendLine("   P.Year, ");
            _sql.AppendLine("   P.LVisit ");
            _sql.AppendLine("FROM tb_Poles P ");

            #endregion

            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            List<EDistributionPole> lstDistributionPoles = new List<EDistributionPole>();

            foreach (DataRow dr in dt.Rows)
            {
                EDistributionPole distributionPole = new EDistributionPole();

                distributionPole.Id = ConvertHelper.ToInt32(dr["PoleId"]);
                distributionPole.ServerID = ConvertHelper.ToInt32(dr["PoleServerID"]);
                distributionPole.Map.Id = ConvertHelper.ToInt32(dr["PolygonMapFk"]);
                distributionPole.Section.Id = ConvertHelper.ToInt32(dr["PolygonSectionFk"]);
                distributionPole.Specie.Id = ConvertHelper.ToInt32(dr["SpecieFk"]);
                distributionPole.Class.Id = ConvertHelper.ToInt32(dr["ClassFk"]);
                distributionPole.Length.Id = ConvertHelper.ToInt32(dr["LengthFk"]);
                distributionPole.Treatment.Id = ConvertHelper.ToInt32(dr["TreatmentFk"]);
                distributionPole.Latitude = ConvertHelper.Todouble(dr["Latitude"]);
                distributionPole.Longitude = ConvertHelper.Todouble(dr["Longitude"]);
                distributionPole.InspBy = ConvertHelper.Tostring(dr["InspBy"]);
                distributionPole.Number = ConvertHelper.Tostring(dr["Number"]);
                distributionPole.D804 = ConvertHelper.Tostring(dr["D804"]);
                distributionPole.Year = ConvertHelper.ToInt32(dr["Year"]);
                distributionPole.AcquisitionDate = ConvertHelper.ToDateTime(dr["AcquisitionDate"]);
                distributionPole.LVisit = ConvertHelper.ToDateTime(dr["LVisit"]);

                distributionPole.Images = LImage.GetPoleImages(distributionPole, connectionString);
                distributionPole.Notes = LNote.GetPoleNotes(distributionPole, connectionString);
                distributionPole.Phases = LPhase.GetPolePhases(distributionPole, connectionString);
                distributionPole.Tops = LTop.GetPoleTops(distributionPole, connectionString);
                distributionPole.InspectionResult = LInspectionResult.GetPoleInspectionResults(distributionPole, connectionString);
                distributionPole.Meters = LMeter.GetPoleMeters(distributionPole, connectionString);

                lstDistributionPoles.Add(distributionPole);
            } 

            return lstDistributionPoles;
        }

        public static List<EDistributionPole> GetDistributionPolesWithDetails(string connectionString)
        {
            StringBuilder _sql = new StringBuilder();

            #region Query

            _sql.AppendLine("SELECT ");
            _sql.AppendLine("   P.PoleId, ");
            _sql.AppendLine("   P.PoleServerID, ");
            _sql.AppendLine("   P.PolygonMapFk, ");
            _sql.AppendLine("   P.PolygonSectionFk, ");
            _sql.AppendLine("   P.SpecieFk, ");
            _sql.AppendLine("   P.ClassFk, ");
            _sql.AppendLine("   P.LengthFk, ");
            _sql.AppendLine("   P.TreatmentFk, ");
            _sql.AppendLine("   P.Latitude, ");
            _sql.AppendLine("   P.Longitude, ");
            _sql.AppendLine("   P.InspBy, ");
            _sql.AppendLine("   P.D804, ");
            _sql.AppendLine("   P.Number, ");
            _sql.AppendLine("   P.AcquisitionDate, ");
            _sql.AppendLine("   P.Year, ");
            _sql.AppendLine("   P.LVisit, ");
            _sql.AppendLine("   P.GuardsQuantity, ");
            _sql.AppendLine("   P.PoleTag, ");
            _sql.AppendLine("   P.GroundWireRepair, ");
            _sql.AppendLine("   P.Billable, ");
            _sql.AppendLine("   P.Circuit, ");
            _sql.AppendLine("   P.Kv, ");
            _sql.AppendLine("   P.ConductorType, ");
            _sql.AppendLine("   P.ACSR, ");
            _sql.AppendLine("   UP.PolygonName MapName, ");
            _sql.AppendLine("   UP1.PolygonName SectionName, ");
            _sql.AppendLine("   LKS.LookupText SpecieName, ");
            _sql.AppendLine("   LKC.LookupText ClassName, ");
            _sql.AppendLine("   LKL.LookupText LengthName, ");
            _sql.AppendLine("   LKT.LookupText TreatmentName, ");
            _sql.AppendLine("   LKK.Text KvName, ");
            _sql.AppendLine("   LKCT.Text ConductorTypeName, ");
            _sql.AppendLine("   LKAC.Text ACSRName");
            _sql.AppendLine("FROM tb_Poles P ");
            _sql.AppendLine("LEFT JOIN tb_Utility_Polygons UP ON P.PolygonMapFk = UP.PolygonId AND UP.PolygonTypeFk = 1");
            _sql.AppendLine("LEFT JOIN tb_Utility_Polygons UP1 ON P.PolygonSectionFk = UP1.PolygonId AND UP1.PolygonTypeFk = 2 ");
            _sql.AppendLine("LEFT JOIN tb_Lookups LKS ON LKS.LookupValue = P.SpecieFk AND LKS.LookupTypeFk = 'Species' ");
            _sql.AppendLine("LEFT JOIN tb_Lookups LKC ON LKC.LookupValue = P.ClassFk AND LKC.LookupTypeFk = 'Class' ");
            _sql.AppendLine("LEFT JOIN tb_Lookups LKL ON LKL.LookupValue = P.LengthFk AND LKL.LookupTypeFk = 'Length' ");
            _sql.AppendLine("LEFT JOIN tb_Lookups LKT ON LKT.LookupValue = P.TreatmentFk AND LKT.LookupTypeFk = 'Treatment' ");
            _sql.AppendLine("LEFT JOIN tb_Transformers_Lookups LKK ON LKK.idLookup = P.Kv AND LKK.Type = " + (int)DistributionLookUpType.Kv);
            _sql.AppendLine("LEFT JOIN tb_Transformers_Lookups LKCT ON LKCT.idLookup = P.Kv AND LKCT.Type = " + (int)DistributionLookUpType.ConductorType);
            _sql.AppendLine("LEFT JOIN tb_Transformers_Lookups LKAC ON LKAC.idLookup = P.Kv AND LKAC.Type = " + (int)DistributionLookUpType.ACSR);

            #endregion

            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            #region Setting values
            _sql = new StringBuilder("SELECT   SettingValue, SettingName, SettingKey FROM tb_Settings WHERE SettingKey in ('3','4', '5', '9','12') ORDER BY SettingKey");
            DataSet dsDBSettings = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString);
            DataTable dtDBSettings = dsDBSettings.Tables[0];
            DistributionType distributionType = DistributionType.GPS;

            EVendor Vendor = new EVendor();
            EVendor_Contact vendorContact = new EVendor_Contact();
            EVendor_Project Project = new EVendor_Project();
            PPI.Entities.Inspections.EInspector Inspector = new PPI.Entities.Inspections.EInspector();
            
            foreach (DataRow dr in dtDBSettings.Rows) {
                switch (dr["SettingKey"].ToString()) {
                    case "3":
                        Project = new EVendor_Project(int.Parse(dr["SettingValue"].ToString()), dr["SettingName"].ToString());
                        break;
                    case "4":
                        Vendor = new EVendor(int.Parse(dr["SettingValue"].ToString()));
                        Vendor.Name = dr["SettingName"].ToString();
                        break;
                    case "5":
                        vendorContact = new EVendor_Contact(int.Parse(dr["SettingValue"].ToString()));
                        vendorContact.Name = dr["SettingName"].ToString();
                        break;
                    case "9":
                        Inspector = new PPI.Entities.Inspections.EInspector(int.Parse(dr["SettingValue"].ToString()), dr["SettingName"].ToString());
                        break;
                    case "12":
                        distributionType = dr["SettingValue"].ToString().Equals("1") ? DistributionType.Inspection : DistributionType.GPS;
                        break;
                }
            }
            #endregion Setting values

            List<EDistributionPole> lstDistributionPoles = new List<EDistributionPole>();

            foreach (DataRow dr in dt.Rows)
            {
                EDistributionPole distributionPole = new EDistributionPole();
                #region Assign Distribution Pole Values
                distributionPole.Id = ConvertHelper.ToInt32(dr["PoleId"]);
                distributionPole.ServerID = ConvertHelper.ToInt32(dr["PoleServerID"]);

                if (dr["PolygonMapFk"] != DBNull.Value)
                {
                    distributionPole.Map.Id = ConvertHelper.ToInt32(dr["PolygonMapFk"]);
                    distributionPole.Map.Name = ConvertHelper.Tostring(dr["MapName"]);
                }
                if (dr["PolygonSectionFk"] != DBNull.Value)
                {
                    distributionPole.Section.Id = ConvertHelper.ToInt32(dr["PolygonSectionFk"]);
                    distributionPole.Section.Name = ConvertHelper.Tostring(dr["SectionName"]);
                }
                if (dr["SpecieFk"] != DBNull.Value)
                {
                    distributionPole.Specie.Id = ConvertHelper.ToInt32(dr["SpecieFk"]);
                    distributionPole.Specie.Name = ConvertHelper.Tostring(dr["SpecieName"]);
                }

                if (dr["ClassFk"] != DBNull.Value) 
                {
                    distributionPole.Class.Id = ConvertHelper.ToInt32(dr["ClassFk"]);
                    distributionPole.Class.Name = ConvertHelper.Tostring(dr["ClassName"]);
                }

                if (dr["LengthFk"] != DBNull.Value)
                {
                    distributionPole.Length.Id = ConvertHelper.ToInt32(dr["LengthFk"]);
                    distributionPole.Length.Name = ConvertHelper.Tostring(dr["LengthName"]);
                }

                if (dr["TreatmentFk"] != DBNull.Value)
                {
                    distributionPole.Treatment.Id = ConvertHelper.ToInt32(dr["TreatmentFk"]);
                    distributionPole.Treatment.Name = ConvertHelper.Tostring(dr["TreatmentName"]);
                }

                if (dr["Kv"] != DBNull.Value)
                {
                    distributionPole.Kv.Id = ConvertHelper.ToInt32(dr["Kv"]);
                    distributionPole.Kv.Text = ConvertHelper.Tostring(dr["KvName"]);
                    distributionPole.Kv.Type = DistributionLookUpType.Kv;
                }

                if (dr["ConductorType"] != DBNull.Value)
                {
                    distributionPole.ConductorType.Id = ConvertHelper.ToInt32(dr["ConductorType"]);
                    distributionPole.ConductorType.Text = ConvertHelper.Tostring(dr["ConductorTypeName"]);
                    distributionPole.ConductorType.Type = DistributionLookUpType.ConductorType;
                }

                if (dr["ACSR"] != DBNull.Value)
                {
                    distributionPole.ACSR.Id = ConvertHelper.ToInt32(dr["ACSR"]);
                    distributionPole.ACSR.Text = ConvertHelper.Tostring(dr["ACSRName"]);
                    distributionPole.ACSR.Type = DistributionLookUpType.ACSR;
                }

                if (dr["Latitude"] != DBNull.Value)
                    distributionPole.Latitude = ConvertHelper.Todouble(dr["Latitude"]);

                if (dr["Longitude"] != DBNull.Value)
                    distributionPole.Longitude = ConvertHelper.Todouble(dr["Longitude"]);

                if (dr["GuardsQuantity"] != DBNull.Value)
                    distributionPole.GuardsQuantity = ConvertHelper.ToInt32(dr["GuardsQuantity"]);

                distributionPole.PoleTag = ConvertHelper.Tobool(dr["PoleTag"]);
                distributionPole.GroundWireRepair = ConvertHelper.Tobool(dr["GroundWireRepair"]);
                distributionPole.Billable = ConvertHelper.Tobool(dr["Billable"]);
                distributionPole.InspBy = ConvertHelper.Tostring(dr["InspBy"]);
                distributionPole.Number = ConvertHelper.Tostring(dr["Number"]);
                distributionPole.Year = ConvertHelper.ToInt32(dr["Year"]);
                distributionPole.D804 = ConvertHelper.Tostring(dr["D804"]);
                distributionPole.AcquisitionDate = ConvertHelper.ToDateTime(dr["AcquisitionDate"]);
                distributionPole.LVisit = ConvertHelper.ToDateTime(dr["LVisit"]);
                distributionPole.Circuit = ConvertHelper.Tostring(dr["Circuit"]);
                distributionPole.DistributionType = distributionType;

                distributionPole.Images = LImage.GetPoleImages(distributionPole, connectionString);
                distributionPole.Notes = LNote.GetPoleNotes(distributionPole, connectionString);
                distributionPole.Phases = LPhase.GetPolePhases(distributionPole, connectionString);
                distributionPole.Tops = LTop.GetPoleTops(distributionPole, connectionString);
                distributionPole.InspectionResult = LInspectionResult.GetPoleInspectionResults(distributionPole, connectionString);
                distributionPole.Meters = LMeter.GetPoleMeters(distributionPole, connectionString);
                distributionPole.Transformers = LTransformer.GetPoleTransformers(distributionPole, connectionString);
                distributionPole.Sectionalizers = LSectionalizer.GetPoleSectionalizers(distributionPole, connectionString);
                distributionPole.Reclosers = LRecloser.GetPoleReclosers(distributionPole, connectionString);

                distributionPole.Project = Project;
                distributionPole.Vendor = Vendor;
                distributionPole.VendorContact = vendorContact;
                distributionPole.Inspector = Inspector;
                #endregion Assign Distribution Pole Values
                lstDistributionPoles.Add(distributionPole);
            }

            return lstDistributionPoles;
        }

        public static List<EDistributionPole> GetDistributionProjectPoles(string connectionString, DistributionType distributionType, Int32 poleId)
        {
            StringBuilder _sql = new StringBuilder();

            #region Query

            _sql.AppendLine("SELECT ");
            _sql.AppendLine("   P.PoleId, ");
            _sql.AppendLine("   P.PolygonMapFk, ");
            _sql.AppendLine("   P.PolygonSectionFk, ");
            _sql.AppendLine("   P.SpecieFk, ");
            _sql.AppendLine("   P.ClassFk, ");
            _sql.AppendLine("   P.LengthFk, ");
            _sql.AppendLine("   P.TreatmentFk, ");
            _sql.AppendLine("   P.Latitude, ");
            _sql.AppendLine("   P.Longitude, ");
            _sql.AppendLine("   P.InspBy, ");
            _sql.AppendLine("   P.D804, ");
            _sql.AppendLine("   P.Number, ");
            _sql.AppendLine("   P.AcquisitionDate, ");
            _sql.AppendLine("   P.Year, ");
            _sql.AppendLine("   P.LVisit, ");
            _sql.AppendLine("   P.DistributionType, ");
            _sql.AppendLine("   P.GuardsQuantity, ");
            _sql.AppendLine("   P.PoleTag, ");
            _sql.AppendLine("   P.Circuit, ");
            _sql.AppendLine("   P.GroundWireRepair, ");
            _sql.AppendLine("   P.Billable, ");
            _sql.AppendLine("   P.Kv, ");
            _sql.AppendLine("   UP.PolygonName MapName, ");
            _sql.AppendLine("   UP1.PolygonName SectionName, ");
            _sql.AppendLine("   LKS.LookupText SpecieName, ");
            _sql.AppendLine("   LKC.LookupText ClassName, ");
            _sql.AppendLine("   LKL.LookupText LengthName, ");
            _sql.AppendLine("   LKT.LookupText TreatmentName, ");
            _sql.AppendLine("   LKK.Text KvName, ");
            _sql.AppendLine("   VEN.VendorId, VEN.Name as VendorName, ");
            _sql.AppendLine("   P.ProjectFK, PRO.Name as ProjectName, ");
            _sql.AppendLine("   INS.InspectorId, INS.Name as InspectorName ");
            _sql.AppendLine("FROM tb_Poles P ");
            _sql.AppendLine("LEFT JOIN tb_Utility_Polygons UP ON P.PolygonMapFk = UP.PolygonId ");
            _sql.AppendLine("LEFT JOIN tb_Utility_Polygons UP1 ON P.PolygonSectionFk = UP1.PolygonId ");
            _sql.AppendLine("LEFT JOIN tb_Lookups LKS ON LKS.LookupValue = P.SpecieFk AND LKS.LookupTypeFk = 'Species' ");
            _sql.AppendLine("LEFT JOIN tb_Lookups LKC ON LKC.LookupValue = P.ClassFk AND LKC.LookupTypeFk = 'Class' ");
            _sql.AppendLine("LEFT JOIN tb_Lookups LKL ON LKL.LookupValue = P.LengthFk AND LKL.LookupTypeFk = 'Length' ");
            _sql.AppendLine("LEFT JOIN tb_Lookups LKT ON LKT.LookupValue = P.TreatmentFk AND LKT.LookupTypeFk = 'Treatment' ");
            _sql.AppendLine("LEFT JOIN tb_Vendor VEN ON VEN.VendorId = P.VendorFK ");
            _sql.AppendLine("LEFT JOIN tb_Projects PRO ON PRO.ProjectId = P.ProjectFK ");
            _sql.AppendLine("LEFT JOIN tb_Inspectors INS ON INS.InspectorId = P.InspectorFK ");
            _sql.AppendLine("LEFT JOIN tb_Transformers_Lookups LKK ON LKK.idLookup = P.Kv AND LKK.Type = " + (int)DistributionLookUpType.Kv);
            _sql.AppendLine("LEFT JOIN tb_Transformers_Lookups LKCT ON LKCT.idLookup = P.Kv AND LKCT.Type = " + (int)DistributionLookUpType.ConductorType);
            _sql.AppendLine("LEFT JOIN tb_Transformers_Lookups LKAC ON LKAC.idLookup = P.Kv AND LKAC.Type = " + (int)DistributionLookUpType.ACSR);
            _sql.AppendLine("WHERE DistributionType = " + (int)distributionType);

            if (poleId != Int32.MinValue)
                _sql.AppendLine(" AND P.PoleId = " + poleId);

            #endregion

            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            //Gets all the entities
            /*
            List<EDistributionPoleImage> images = LImage.GetProjectPoleImages(connectionString);
            List<ENote> notes = LNote.GetProjectPoleNotes(connectionString);
            List<EPhase> phases = LPhase.GetProjectPolePhases(connectionString);
            List<EXMissionStructures_TopType> tops = LTop.GetProjectPoleTops(connectionString);
            List<EDistributionInspectionResult> inspectionResult = LInspectionResult.GetProjectPoleInspectionResults(connectionString);
            List<EMeter> meters = LMeter.GetProjectPoleMeters(connectionString);
            */

            List<EDistributionPole> lstDistributionPoles = new List<EDistributionPole>();
            foreach (DataRow dr in dt.Rows)
            {
                EDistributionPole distributionPole = new EDistributionPole();
                #region Assign Distribution Pole Values
                distributionPole.Id = ConvertHelper.ToInt32(dr["PoleId"]);

                if (dr["PolygonMapFk"] != DBNull.Value)
                {
                    distributionPole.Map.Id = ConvertHelper.ToInt32(dr["PolygonMapFk"]);
                    distributionPole.Map.Name = ConvertHelper.Tostring(dr["MapName"]);
                }
                if (dr["PolygonSectionFk"] != DBNull.Value)
                {
                    distributionPole.Section.Id = ConvertHelper.ToInt32(dr["PolygonSectionFk"]);
                    distributionPole.Section.Name = ConvertHelper.Tostring(dr["SectionName"]);
                }
                if (dr["SpecieFk"] != DBNull.Value)
                {
                    distributionPole.Specie.Id = ConvertHelper.ToInt32(dr["SpecieFk"]);
                    distributionPole.Specie.Name = ConvertHelper.Tostring(dr["SpecieName"]);
                }

                if (dr["ClassFk"] != DBNull.Value)
                {
                    distributionPole.Class.Id = ConvertHelper.ToInt32(dr["ClassFk"]);
                    distributionPole.Class.Name = ConvertHelper.Tostring(dr["ClassName"]);
                }

                if (dr["LengthFk"] != DBNull.Value)
                {
                    distributionPole.Length.Id = ConvertHelper.ToInt32(dr["LengthFk"]);
                    distributionPole.Length.Name = ConvertHelper.Tostring(dr["LengthName"]);
                }

                if (dr["TreatmentFk"] != DBNull.Value)
                {
                    distributionPole.Treatment.Id = ConvertHelper.ToInt32(dr["TreatmentFk"]);
                    distributionPole.Treatment.Name = ConvertHelper.Tostring(dr["TreatmentName"]);
                }
                
                if (dr["Kv"] != DBNull.Value)
                {
                    distributionPole.Kv.Id = ConvertHelper.ToInt32(dr["Kv"]);
                    distributionPole.Kv.Text = ConvertHelper.Tostring(dr["KvName"]);
                    distributionPole.Kv.Type = DistributionLookUpType.Kv;
                }

                if (dr["ConductorType"] != DBNull.Value)
                {
                    distributionPole.ConductorType.Id = ConvertHelper.ToInt32(dr["ConductorType"]);
                    distributionPole.ConductorType.Text = ConvertHelper.Tostring(dr["ConductorTypeName"]);
                    distributionPole.ConductorType.Type = DistributionLookUpType.ConductorType;
                }

                if (dr["ACSR"] != DBNull.Value)
                {
                    distributionPole.ConductorType.Id = ConvertHelper.ToInt32(dr["ACSR"]);
                    distributionPole.ConductorType.Text = ConvertHelper.Tostring(dr["ACSRName"]);
                    distributionPole.ConductorType.Type = DistributionLookUpType.ACSR;
                }

                if (dr["Latitude"] != DBNull.Value)
                    distributionPole.Latitude = ConvertHelper.Todouble(dr["Latitude"]);

                if (dr["Longitude"] != DBNull.Value)
                    distributionPole.Longitude = ConvertHelper.Todouble(dr["Longitude"]);

                if (dr["GuardsQuantity"] != DBNull.Value)
                    distributionPole.GuardsQuantity = ConvertHelper.ToInt32(dr["GuardsQuantity"]);

                distributionPole.PoleTag = ConvertHelper.Tobool(dr["PoleTag"]);
                distributionPole.GroundWireRepair = ConvertHelper.Tobool(dr["GroundWireRepair"]);
                distributionPole.Billable = ConvertHelper.Tobool(dr["Billable"]);
                distributionPole.InspBy = ConvertHelper.Tostring(dr["InspBy"]);
                distributionPole.Number = ConvertHelper.Tostring(dr["Number"]);
                distributionPole.D804 = ConvertHelper.Tostring(dr["D804"]);
                distributionPole.Year = ConvertHelper.ToInt32(dr["Year"]);
                distributionPole.Circuit = ConvertHelper.Tostring(dr["Circuit"]);
                distributionPole.AcquisitionDate = ConvertHelper.ToDateTime(dr["AcquisitionDate"]);
                distributionPole.LVisit = ConvertHelper.ToDateTime(dr["LVisit"]);
                distributionPole.DistributionType = (DistributionType) ConvertHelper.ToInt32(dr["DistributionType"]);
                distributionPole.isProjectPole = true;

                StringBuilder _sql2 = new StringBuilder();

                #region Query

                _sql2.AppendLine("SELECT ");
                _sql2.AppendLine("   PI.ImageId, ");
                _sql2.AppendLine("   PI.FileName ");
                _sql2.AppendLine("FROM tb_PoleImages PI ");
                _sql2.AppendLine("WHERE PI.PoleFk = " + distributionPole.Id + ";");

                _sql2.AppendLine("SELECT ");
                _sql2.AppendLine("   PR.PoleResult, ");
                _sql2.AppendLine("   PR.ResultTypeFk, ");
                _sql2.AppendLine("   LKS.LookupText ");
                _sql2.AppendLine("FROM tb_PoleResults PR ");
                _sql2.AppendLine("LEFT JOIN tb_Lookups LKS ON LKS.LookupValue = PR.ResultTypeFk AND LKS.LookupTypeFk = 'InspectionResult' ");
                _sql2.AppendLine("WHERE PR.PoleFk = " + distributionPole.Id + ";");

                #endregion
                
                distributionPole.Images = LImage.GetProjectPoleImages(distributionPole, connectionString);
                distributionPole.Notes = LNote.GetProjectPoleNotes(distributionPole, connectionString);
                distributionPole.Phases = LPhase.GetProjectPolePhases(distributionPole, connectionString);
                distributionPole.Tops = LTop.GetProjectPoleTops(distributionPole, connectionString);
                distributionPole.InspectionResult = LInspectionResult.GetProjectPoleInspectionResults(distributionPole, connectionString);
                distributionPole.Meters = LMeter.GetProjectPoleMeters(distributionPole, connectionString);

                distributionPole.Vendor = new EVendor(ConvertHelper.ToInt32(dr["VendorId"]));
                distributionPole.Vendor.Name = dr["VendorName"].ToString();
                distributionPole.Project = new EVendor_Project(ConvertHelper.ToInt32(dr["ProjectFk"]), dr["ProjectName"].ToString());
                distributionPole.Inspector = new PPI.Entities.Inspections.EInspector(ConvertHelper.ToInt32(dr["InspectorId"]), dr["InspectorName"].ToString());

                #endregion Assign Distribution Pole Values
                lstDistributionPoles.Add(distributionPole);
            }

            return lstDistributionPoles;
        }

        public static bool CheckIsValidPole(EDistributionPole pole, ref string errorMessage)
        {
            StringBuilder _tmpErrorMessage = new StringBuilder();
            bool _foundedErrors = false;

            if (pole.Latitude == double.MinValue || pole.Longitude == double.MinValue)
            {
                _tmpErrorMessage.AppendLine("Latitude or Longitude is incomplete in Pole " + pole.Id);
                _foundedErrors = true;
            }

            foreach (EMeter meter in pole.Meters)
            {
                if (meter.Latitude == double.MinValue || meter.Longitude == double.MinValue)
                {
                    _tmpErrorMessage.AppendLine("Latitude or Longitude is incomplete in Meter " + meter.Id);
                    _foundedErrors = true;
                }
            }
            errorMessage = _tmpErrorMessage.ToString();
            
            return _foundedErrors;
        }
        
        public static void Update(string connectionString, EDistributionPole pole)
        {
            StringBuilder query = new StringBuilder();
            List<SqlParameter> listParameters = new List<SqlParameter>();

            try
            {
                #region - Pole INSERT -

                query.AppendLine("Update tb_Poles SET ");
                query.AppendLine("PolygonMapFk = @PolygonMapFk,");
                query.AppendLine("PolygonSectionFk = @PolygonSectionFk,");
                query.AppendLine("SpecieFk = @SpecieFk,");
                query.AppendLine("ClassFk = @ClassFk,");
                query.AppendLine("LengthFk = @LengthFk,");
                query.AppendLine("TreatmentFk = @TreatmentFk,");
                query.AppendLine("Latitude = @Latitude,");
                query.AppendLine("Longitude = @Longitude,");
                query.AppendLine("InspBy = @InspBy,");
                query.AppendLine("Number = @Number,");
                query.AppendLine("Kv = @Kv,");
                query.AppendLine("ConductorType = @ConductorType,");
                query.AppendLine("ACSR = @ACSR,");
                query.AppendLine("Circuit = @Circuit,");
                query.AppendLine("AcquisitionDate = @AcquisitionDate,");
                query.AppendLine("D804 = @D804,");
                query.AppendLine("GuardsQuantity = @GuardsQuantity,");
                query.AppendLine("GroundWireRepair = @GroundWireRepair,");
                query.AppendLine("PoleTag = @PoleTag,");
                query.AppendLine("Billable = @Billable,");
                query.AppendLine("Year = @Year, LVisit = @LVisit ");
                query.AppendLine("WHERE PoleId = @PoleId");

                listParameters.Add(DBHelper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));

                if (pole.Map != null && pole.Map.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@PolygonMapFk", ParameterDirection.Input, false, pole.Map.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@PolygonMapFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Section != null && pole.Section.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@PolygonSectionFk", ParameterDirection.Input, false, pole.Section.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@PolygonSectionFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Specie != null && pole.Specie.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, false, pole.Specie.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Class != null && pole.Class.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, pole.Class.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Length != null && pole.Length.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@LengthFk", ParameterDirection.Input, false, pole.Length.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@LengthFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Treatment != null && pole.Treatment.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, pole.Treatment.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Latitude != Double.MinValue)
                    listParameters.Add(DBHelper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, pole.Latitude));
                else
                    listParameters.Add(DBHelper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                if (pole.Longitude != Double.MinValue)
                    listParameters.Add(DBHelper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, pole.Longitude));
                else
                    listParameters.Add(DBHelper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                if (pole.InspBy != string.Empty)
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@InspBy", 100, ParameterDirection.Input, false, pole.InspBy));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@InspBy", 100, ParameterDirection.Input, false, DBNull.Value));

                if (pole.Number != string.Empty)
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, false, pole.Number));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, false, DBNull.Value));

                if (pole.LVisit != DateTime.MinValue)
                    listParameters.Add(DBHelper.CreateDateTimeParameter("@LVisit", ParameterDirection.Input, false, pole.LVisit));
                else
                    listParameters.Add(DBHelper.CreateDateTimeParameter("@LVisit", ParameterDirection.Input, false, DBNull.Value));

                if (pole.D804 != string.Empty)
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@D804", 100, ParameterDirection.Input, false, pole.D804));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@D804", 100, ParameterDirection.Input, false, DBNull.Value));

                if (pole.GuardsQuantity > 0 && pole.GuardsQuantity != int.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, false, pole.GuardsQuantity));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, false, DBNull.Value));

                listParameters.Add(DBHelper.CreateBitParameter("@GroundWireRepair", ParameterDirection.Input, false, pole.GroundWireRepair));
                listParameters.Add(DBHelper.CreateBitParameter("@PoleTag", ParameterDirection.Input, false, pole.PoleTag));
                listParameters.Add(DBHelper.CreateBitParameter("@Billable", ParameterDirection.Input, false, pole.Billable));

                listParameters.Add(DBHelper.CreateDateTimeParameter("@AcquisitionDate", ParameterDirection.Input, false, DateTime.Now));

                if (pole.Year != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, false, pole.Year));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Kv != null && pole.Kv.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@Kv", ParameterDirection.Input, false, pole.Kv.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@Kv", ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(pole.Circuit))
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Circuit", 100,  ParameterDirection.Input, false, pole.Circuit));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Circuit", 100, ParameterDirection.Input, false, DBNull.Value));

                if (pole.ConductorType != null && pole.ConductorType.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@ConductorType", ParameterDirection.Input, false, pole.ConductorType.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@ConductorType", ParameterDirection.Input, false, DBNull.Value));

                if (pole.ACSR != null && pole.ACSR.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@ACSR", ParameterDirection.Input, false, pole.ACSR.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@ACSR", ParameterDirection.Input, false, DBNull.Value));


                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                #endregion

                #region - Phases UPDATE -

                query = new StringBuilder("DELETE tb_PhasesXPoles WHERE PoleFk = @PoleFk");
                listParameters.Clear();
                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                foreach (EPhase phase in pole.Phases)
                {
                    query = new StringBuilder("INSERT INTO tb_PhasesXPoles (PoleFk, PhaseFk) VALUES (");
                    query.AppendLine("@PoleFk, @PhaseFk)");

                    listParameters.Clear();
                    listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                    listParameters.Add(DBHelper.CreateIntParameter("@PhaseFk", ParameterDirection.Input, false, phase.Id));

                    LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());
                }

                #endregion

                #region - Tops UPDATE -

                query = new StringBuilder("DELETE tb_TopsXPoles WHERE PoleFk = @PoleFk");
                listParameters.Clear();
                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                foreach (PPI.Entities.Lookup.EXMissionStructures_PoleTopType top in pole.Tops)
                {
                    query = new StringBuilder("INSERT INTO tb_TopsXPoles (PoleFk, TopFk) VALUES (");
                    query.AppendLine("@PoleFk, @TopFk)");

                    listParameters.Clear();
                    listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                    listParameters.Add(DBHelper.CreateIntParameter("@TopFk", ParameterDirection.Input, false, top.Id));

                    LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());
                }

                #endregion

                #region - Image UPDATE -

                query = new StringBuilder("DELETE tb_PoleImages WHERE PoleFk = @PoleFk");
                listParameters.Clear();
                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                int imagesCount = 0;

                foreach (EDistributionPoleImage image in pole.Images)
                {
                    query = new StringBuilder("INSERT INTO tb_PoleImages (PoleFk, FileName) VALUES (");
                    query.AppendLine("@PoleFk, @FileName)");

                    image.GenerateNewName(pole.Id.ToString() + imagesCount);
                    listParameters.Clear();
                    listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@FileName", 150, ParameterDirection.Input, false, image.FileName));

                    LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());
                    SaveImage(image, connectionString);
                    imagesCount++;
                }

                #endregion

                #region - Notes UPDATE -

                query = new StringBuilder("DELETE tb_PoleResults WHERE PoleFk = @PoleFk");
                listParameters.Clear();
                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                foreach (ENote note in pole.Notes)
                {
                    query = new StringBuilder("INSERT INTO tb_PoleResults (PoleFk, ResultTypeFk) VALUES (");
                    query.AppendLine("@PoleFk, @ResultTypeFk)");

                    listParameters.Clear();
                    listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                    listParameters.Add(DBHelper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, note.Id));

                    LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());
                }

                #endregion

                #region - InspectionResult INSERT -

                query = new StringBuilder("UPDATE tb_Pole_InspectionResult ");
                query.AppendLine("SET PoleFk = @PoleFk, ");
                query.AppendLine("InspectionTypeFk = @InspectionTypeFk, ");
                query.AppendLine("Comments = @Comments, ");
                query.AppendLine("isElectrical = @isElectrical, "); 
                query.AppendLine("ResultTypeFk = @ResultTypeFk ");
                query.AppendLine("WHERE PoleFk = @PoleFk");

                listParameters.Clear();

                if (pole.InspectionResult.InspectionType != null && pole.InspectionResult.InspectionType.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@InspectionTypeFk", ParameterDirection.Input, false, pole.InspectionResult.InspectionType.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@InspectionTypeFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.InspectionResult.Resultype != null && pole.InspectionResult.Resultype.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, pole.InspectionResult.Resultype.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, DBNull.Value));

                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                listParameters.Add(DBHelper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, pole.InspectionResult.Comments));
                listParameters.Add(DBHelper.CreateBitParameter("@isElectrical", ParameterDirection.Input, false, pole.InspectionResult.IsElectrical));

                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                #endregion
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static Int32 SaveImage(EDistributionPoleImage image, string connectionString)
        {
            Int32 returnId = image.Id;

            if (image.Id == Int32.MinValue)
            {
                string newImageName = image.FileName;
                DirHelper.CopyImageToOutbox(connectionString, image.FullName, newImageName);
            }

            return returnId;
        }

        public static void Save(string connectionString, EDistributionPole pole)
        {
            StringBuilder query = new StringBuilder();
            List<SqlParameter> listParameters = new List<SqlParameter>();

            try
            {
                #region - Pole INSERT -

                query.AppendLine("INSERT INTO tb_Poles (PoleId, PolygonMapFk, PolygonSectionFk, SpecieFk, ClassFk, LengthFk, TreatmentFk, Latitude, Longitude, InspBy, Number, AcquisitionDate, Year, LVisit, D804, DistributionType, VendorFk, InspectorFk, ProjectFk, GuardsQuantity, PoleTag, GroundWireRepair, Kv, ConductorType, ACSR, Circuit, Billable) VALUES (");
                query.AppendLine("@PoleId,");
                query.AppendLine("@PolygonMapFk,");
                query.AppendLine("@PolygonSectionFk,");
                query.AppendLine("@SpecieFk,");
                query.AppendLine("@ClassFk,");
                query.AppendLine("@LengthFk,");
                query.AppendLine("@TreatmentFk,");
                query.AppendLine("@Latitude,");
                query.AppendLine("@Longitude,");
                query.AppendLine("@InspBy,");
                query.AppendLine("@Number,");
                query.AppendLine("@AcquisitionDate,");
                query.AppendLine("@Year,");
                query.AppendLine("@LVisit,");
                query.AppendLine("@D804,");
                query.AppendLine("@DistributionType,");
                query.AppendLine("@VendorFk,");
                query.AppendLine("@InspectorFk,");
                query.AppendLine("@ProjectFk, ");
                query.AppendLine("@GuardsQuantity,");
                query.AppendLine("@PoleTag,");
                query.AppendLine("@GroundWireRepair,");
                query.AppendLine("@Kv,");
                query.AppendLine("@ConductorType,");
                query.AppendLine("@ACSR,");
                query.AppendLine("@Circuit,");
                query.AppendLine("@Billable) ");

                listParameters.Clear();
                if (pole.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Map != null && pole.Map.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@PolygonMapFk", ParameterDirection.Input, false, pole.Map.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@PolygonMapFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Section != null && pole.Section.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@PolygonSectionFk", ParameterDirection.Input, false, pole.Section.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@PolygonSectionFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Specie != null && pole.Specie.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, false, pole.Specie.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@SpecieFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Class != null && pole.Class.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, pole.Class.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@ClassFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Length != null && pole.Length.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@LengthFk", ParameterDirection.Input, false, pole.Length.Id ));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@LengthFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Treatment != null && pole.Treatment.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, pole.Treatment.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@TreatmentFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Latitude != Double.MinValue)
                    listParameters.Add(DBHelper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, pole.Latitude));
                else
                    listParameters.Add(DBHelper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                if (pole.Longitude != Double.MinValue)
                    listParameters.Add(DBHelper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, pole.Longitude));
                else
                    listParameters.Add(DBHelper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                if (pole.InspBy != string.Empty)
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@InspBy", 100, ParameterDirection.Input, false, pole.InspBy));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@InspBy", 100, ParameterDirection.Input, false, DBNull.Value));

                if (pole.Number != string.Empty)
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, false, pole.Number));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Number", 100, ParameterDirection.Input, false, DBNull.Value));

                if (pole.LVisit != DateTime.MinValue)
                    listParameters.Add(DBHelper.CreateDateTimeParameter("@LVisit", ParameterDirection.Input, false, pole.LVisit));
                else
                    listParameters.Add(DBHelper.CreateDateTimeParameter("@LVisit", ParameterDirection.Input, false, DBNull.Value));

                listParameters.Add(DBHelper.CreateDateTimeParameter("@AcquisitionDate", ParameterDirection.Input, false, DateTime.Now));

                if (pole.Year != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, false, pole.Year));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@Year", ParameterDirection.Input, false, DBNull.Value));

                if (!String.IsNullOrEmpty(pole.D804))
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@D804", 250, ParameterDirection.Input, false, pole.D804));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@D804", 250, ParameterDirection.Input, false, DBNull.Value));

                if (pole.DistributionType != DistributionType.None)
                    listParameters.Add(DBHelper.CreateIntParameter("@DistributionType", ParameterDirection.Input, false, (int)pole.DistributionType));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@DistributionType", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Vendor.Id != int.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@VendorFk", ParameterDirection.Input, false, pole.Vendor.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@VendorFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Inspector.Id != int.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@InspectorFk", ParameterDirection.Input, false, pole.Inspector.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@InspectorFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.Project.Id != int.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@ProjectFk", ParameterDirection.Input, false, pole.Project.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@ProjectFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.GuardsQuantity != int.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, false, pole.GuardsQuantity));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@GuardsQuantity", ParameterDirection.Input, false, DBNull.Value));

                listParameters.Add(DBHelper.CreateBitParameter("@PoleTag", ParameterDirection.Input, false, pole.PoleTag));
                listParameters.Add(DBHelper.CreateBitParameter("@GroundWireRepair", ParameterDirection.Input, false, pole.GroundWireRepair));
                
                if (pole.Kv != null && pole.Kv.Id != int.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@Kv", ParameterDirection.Input, false, pole.Kv.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@Kv", ParameterDirection.Input, false, DBNull.Value));

                if (pole.ConductorType != null && pole.ConductorType.Id != int.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@ConductorType", ParameterDirection.Input, false, pole.ConductorType.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@ConductorType", ParameterDirection.Input, false, DBNull.Value));

                if (pole.ACSR != null && pole.ACSR.Id != int.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@ACSR", ParameterDirection.Input, false, pole.ACSR.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@ACSR", ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(pole.Circuit))
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Circuit", 100, ParameterDirection.Input, false, pole.Circuit));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Circuit", 100, ParameterDirection.Input, false, DBNull.Value));

                listParameters.Add(DBHelper.CreateBitParameter("@Billable", ParameterDirection.Input, false, pole.Billable));

                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                //Gets the last inserted PoleId 
                Int32 lastId = pole.Id;

                if (pole.Id == Int32.MinValue) 
                {
                    query = new StringBuilder();
                    query.AppendLine("SELECT MAX(PoleId) AS id FROM tb_Poles");

                    lastId = (int)LMobileDatabase.ExecuteScalar(query.ToString(), connectionString);
                }

                #endregion

                #region - Phases INSERT -

                query = new StringBuilder();
                query.AppendLine("SELECT MAX(PhaseXPoleId) AS id FROM tb_PhasesXPoles");
                Int32 tableLastId = (int)LMobileDatabase.ExecuteScalar(query.ToString(), connectionString);
                int cont = 0;

                foreach (EPhase phase in pole.Phases)
                {
                    query = new StringBuilder("INSERT INTO tb_PhasesXPoles (PhaseXPoleId, PoleFk, PhaseFk) VALUES (");
                    query.AppendLine("@PhaseXPoleId, @PoleFk, @PhaseFk)");

                    cont++;
                    listParameters.Clear();
                    listParameters.Add(DBHelper.CreateIntParameter("@PhaseXPoleId", ParameterDirection.Input, false, tableLastId + cont));
                    listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, lastId));
                    listParameters.Add(DBHelper.CreateIntParameter("@PhaseFk", ParameterDirection.Input, false, phase.Id));

                    LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());
                }

                #endregion

                #region - Tops INSERT -

                query = new StringBuilder();
                query.AppendLine("SELECT MAX(TopXPoleId) AS id FROM tb_TopsXPoles");
                tableLastId = (int)LMobileDatabase.ExecuteScalar(query.ToString(), connectionString);
                cont = 0;

                foreach (EXMissionStructures_PoleTopType top in pole.Tops)
                {
                    query = new StringBuilder("INSERT INTO tb_TopsXPoles (TopXPoleId, PoleFk, TopFk) VALUES (");
                    query.AppendLine("@TopXPoleId, @PoleFk, @TopFk)");

                    cont++;
                    listParameters.Clear();
                    listParameters.Add(DBHelper.CreateIntParameter("@TopXPoleId", ParameterDirection.Input, false, tableLastId + cont));
                    listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, lastId));
                    listParameters.Add(DBHelper.CreateIntParameter("@TopFk", ParameterDirection.Input, false, top.Id));

                    LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());
                }

                #endregion

                #region - Image INSERT -

                int imagesCount = 0;

                foreach (EDistributionPoleImage image in pole.Images)
                {
                    query = new StringBuilder("INSERT INTO tb_PoleImages (ImageId, PoleFk, FileName) VALUES (");
                    query.AppendLine("@ImageId, @PoleFk, @FileName)");

                    if (image.Id == int.MinValue)
                        image.GenerateNewName(lastId.ToString() + imagesCount);

                    listParameters.Clear();
                    listParameters.Add(DBHelper.CreateIntParameter("@ImageId", ParameterDirection.Input, false, image.Id));
                    listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, lastId));
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@FileName", 150, ParameterDirection.Input, false, image.FileName));

                    LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                    imagesCount++;
                }

                #endregion

                #region - Notes INSERT -

                query = new StringBuilder();
                query.AppendLine("SELECT MAX(PoleResult) AS id FROM tb_PoleResults");
                tableLastId = (int)LMobileDatabase.ExecuteScalar(query.ToString(), connectionString);
                cont = 0;

                foreach (ENote note in pole.Notes)
                {
                    query = new StringBuilder("INSERT INTO tb_PoleResults (PoleResult, PoleFk, ResultTypeFk) VALUES (");
                    query.AppendLine("@PoleResult, @PoleFk, @ResultTypeFk)");

                    cont++;
                    listParameters.Clear();
                    listParameters.Add(DBHelper.CreateIntParameter("@PoleResult", ParameterDirection.Input, false, tableLastId + cont));
                    listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, lastId));
                    listParameters.Add(DBHelper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, note.Id));

                    LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());
                }

                #endregion

                #region - InspectionResult INSERT -

                query = new StringBuilder("INSERT INTO tb_Pole_InspectionResult (PoleInspectionResultId, PoleFk, InspectionTypeFk, Comments, isElectrical, ResultTypeFk) VALUES (");
                query.AppendLine("@PoleInspectionResultId, @PoleFk, @InspectionTypeFk, @Comments, @isElectrical, @ResultTypeFk)");
                listParameters.Clear();

                if (pole.InspectionResult.ServerId != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@PoleInspectionResultId", ParameterDirection.Input, false, pole.InspectionResult.ServerId));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@PoleInspectionResultId", ParameterDirection.Input, false, DBNull.Value));

                if (pole.InspectionResult.InspectionType != null && pole.InspectionResult.InspectionType.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@InspectionTypeFk", ParameterDirection.Input, false, pole.InspectionResult.InspectionType.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@InspectionTypeFk", ParameterDirection.Input, false, DBNull.Value));

                if (pole.InspectionResult.Resultype != null && pole.InspectionResult.Resultype.Id != Int32.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, pole.InspectionResult.Resultype.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, DBNull.Value));

                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, lastId));
                listParameters.Add(DBHelper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, pole.InspectionResult.Comments));
                listParameters.Add(DBHelper.CreateBitParameter("@isElectrical", ParameterDirection.Input, false, pole.InspectionResult.IsElectrical));

                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                #endregion

                pole.Id = lastId;
            }
            catch (Exception exc)
            {
                throw;
            }
        }

        public static void Delete(EDistributionPole pole)
        {
             WebConsumer.Distribution.WPole.DeletePole(pole.Id);
        }

        public static void Delete(string connectionString, EDistributionPole pole)
        {
            StringBuilder query = new StringBuilder();
            List<SqlParameter> listParameters = new List<SqlParameter>();

            try
            {
                #region - Pole DELETE -

                query.AppendLine("DELETE tb_Poles ");
                query.AppendLine("WHERE PoleId = @PoleId");

                listParameters.Clear();
                listParameters.Add(DBHelper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));

                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                #endregion

                #region - Phases DELETE -

                query = new StringBuilder("DELETE tb_PhasesXPoles WHERE PoleFk = @PoleFk");
                listParameters.Clear();
                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));

                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                #endregion

                #region - Tops UPDATE -

                query = new StringBuilder("DELETE tb_TopsXPoles WHERE PoleFk = @PoleFk");
                listParameters.Clear();
                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                
                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                #endregion

                #region - Image UPDATE -

                query = new StringBuilder("DELETE tb_PoleImages WHERE PoleFk = @PoleFk");
                listParameters.Clear();
                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                
                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                #endregion

                #region - Notes UPDATE -

                query = new StringBuilder("DELETE tb_PoleResults WHERE PoleFk = @PoleFk");
                listParameters.Clear();
                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));
                
                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                #endregion

                #region - InspectionResult INSERT -

                query = new StringBuilder("DELETE tb_Pole_InspectionResult ");
                query.AppendLine("WHERE PoleFk = @PoleFk");

                listParameters.Clear();
                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));

                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                #endregion
            }
            catch (Exception exc)
            {
                throw exc;
            }
        }
    }
}

